package com.hsbc.dev.teamo4.sms.common.utils.factory;

import com.google.common.collect.Lists;
import com.hsbc.dev.teamo4.sms.sm.po.User;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author:Nina
 * @version:
 * @Project: permission_manage
 * @Package: com.hsbc.dev.teamo4.sms.common.utils.factory
 * @Description:
 * @Date date: 2018/7/27
 */
public class POIFactory {

    /**
     * 导出用户信息
     *
     * @param userList
     */
    public void exportUserExcel(List<User> userList) {

        try {
            //1.创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //1.1创建合并单元格对象
            CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, 10);
            //1.2头标题样式
            HSSFCellStyle headStyle = createCellStyle(workbook, (short) 16);
            //1.3列标题样式
            HSSFCellStyle colStyle = createCellStyle(workbook, (short) 13);
            //2.创建工作表
            HSSFSheet sheet = workbook.createSheet("用户列表");
            //2.1加载合并单元格对象
            sheet.addMergedRegion(callRangeAddress);
            //设置默认列宽
            sheet.setDefaultColumnWidth(25);
            //3.创建行
            //3.1创建头标题行;并且设置头标题
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            //加载单元格样式
            cell.setCellStyle(headStyle);
            cell.setCellValue("用户列表");
            //3.2创建列标题;并且设置列标题
            HSSFRow row2 = sheet.createRow(1);
            String[] titles = {"用户编号", "用户姓名", "用户性别", "用户电话", "电子邮箱", "账户编号",
                    "创建编号", "创建时间", "更新编号", "更新时间", "是否有效"};
            for (int i = 0; i < titles.length; i++) {
                HSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titles[i]);
            }
            //4.操作单元格;将用户列表写入excel
            if (userList != null) {
                for (int j = 0; j < userList.size(); j++) {
                    //创建数据行,前面有两行,头标题行和列标题行
                    HSSFRow row3 = sheet.createRow(j + 2);

                    HSSFCell cell1 = row3.createCell(0);
                    cell1.setCellValue(userList.get(j).getId());

                    HSSFCell cell2 = row3.createCell(1);
                    cell2.setCellValue(userList.get(j).getName());

                    HSSFCell cell3 = row3.createCell(2);
                    cell3.setCellValue(userList.get(j).getUserSex() ? "男" : "女");

                    HSSFCell cell4 = row3.createCell(3);
                    cell4.setCellValue(userList.get(j).getUserPhone());

                    HSSFCell cell5 = row3.createCell(4);
                    cell5.setCellValue(userList.get(j).getUserEmail());

                    HSSFCell cell6 = row3.createCell(5);
                    cell6.setCellValue(userList.get(j).getAccountId());

                    HSSFCell cell7 = row3.createCell(6);
                    cell7.setCellValue(userList.get(j).getCreateId());

                    HSSFCell cell8 = row3.createCell(7);
                    String time1;
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    long lt1 = new Long(userList.get(j).getCreateTime());
                    Date date1 = new Date(lt1);
                    time1 = simpleDateFormat.format(date1);
                    cell8.setCellValue(time1);

                    HSSFCell cell9 = row3.createCell(8);
                    cell9.setCellValue(userList.get(j).getUpdateId());

                    HSSFCell cell10 = row3.createCell(9);
                    String time2;
                    long lt2 = new Long(userList.get(j).getUpdateTime());
                    Date date2 = new Date(lt2);
                    time2 = simpleDateFormat.format(date2);
                    cell10.setCellValue(time2);

                    HSSFCell cell11 = row3.createCell(10);
                    cell11.setCellValue(userList.get(j).getIsEnable());
                }
            }
            //5.输出到输出Excel文件
            FileOutputStream output = new FileOutputStream("D:\\test\\users.xls");
            workbook.write(output);
            output.flush();
            output.close();


        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * @Description 设置单元格格式
     *
     * @param workbook
     * @param fontsize
     * @return
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize) {
        // TODO Auto-generated method stub
        HSSFCellStyle style = workbook.createCellStyle();
        //水平居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //创建字体
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }

    /**
     * 导入用户Excel
     * @param xlsPath
     * @return
     * @throws IOException
     */
    public List<User> loadUserinfo(String xlsPath) throws IOException {
        List<User> temp = Lists.newArrayList();
        FileInputStream fileIn = new FileInputStream(xlsPath);
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook wb0 = new HSSFWorkbook(fileIn);
        //获取Excel文档中的第一个表单
        Sheet sht0 = wb0.getSheetAt(0);
        //对Sheet中的每一行进行迭代
        for (Row r : sht0) {
            //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
            if (r.getRowNum() < 1) {
                continue;
            }
            User info = new User();
            //取出当前行第1个单元格数据，并封装在info实体用户属性上
            info.setId(r.getCell(0).getStringCellValue());
            info.setName(r.getCell(1).getStringCellValue());
            info.setUserSex(r.getCell(2).getStringCellValue().equals("男"));
            info.setUserPhone(r.getCell(3).getStringCellValue());
            info.setUserEmail(r.getCell(4).getStringCellValue());
            info.setAccountId(r.getCell(5).getStringCellValue());
            info.setCreateId(r.getCell(6).getStringCellValue());
            info.setCreateTime(r.getCell(7).getStringCellValue());
            info.setUpdateId(r.getCell(8).getStringCellValue());
            info.setUpdateTime(r.getCell(9).getStringCellValue());
            //String flag=r.getCell(10).getStringCellValue();
            //info.setIsEnable(flag);
            //Boolean flag=r.getCell(10).getBooleanCellValue();
            //info.setIsEnable(flag);
            temp.add(info);
        }
        fileIn.close();
        return temp;
    }
}
